const express = require('express');
const policyRouter = express.Router();
const { sendResult } = require('../utils/index')

const mysql = require('mysql')
const db = mysql.createPool({
    host: "127.0.0.1",
    user: "root",
    password: "admin123",
    database: "mydatabase_01",
    port: 3306,
})


const table = {
    name: 'policy',
}

policyRouter.use(express.json());
policyRouter.use(express.urlencoded({ extended: true }));

//贫困人数变化
policyRouter.get('/policy/list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${table.name}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }
        })
    }
})

//新增 修改
policyRouter.post('/policy/edit', (req, res) => {
    let { id } = req.query
    let { year, policys, news, serves, latest } = req.body
    console.log(req.body);

    if (id) {
        if (year && policys && news && serves) {
            db.query(`UPDATE ${table.name} SET year = '${year}',policys = '${policys}',news = '${news}', serves = '${serves}', latest='${latest}'   WHERE id = ${id}`, (err, result) => {
                if (err) {
                    res.status(500).send(err.response)
                } else {
                    sendResult(res, {
                        msg: '修改成功'
                    })
                }
            }
            )
        } else {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        }
    } else {
        if (!year || !policys || !news || !serves || !latest) {

            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)

        } else {
            db.query(`INSERT INTO ${table.name} (year,  policys, news, serves, latest) VALUES ('${year}', '${policys}', '${news}', '${serves}' ,'${latest}')`, (err, result) => {
                if (err) {
                    console.log(err);
                    if (err.errno === 1062) {
                        sendResult(res, {
                            msg: '该数据已存在'
                        }, 201)
                    } else {
                        res.status(500).send(err)
                    }

                } else {
                    sendResult(res, {
                        msg: '新增成功！'
                    })
                }
            })
        }

    }

})

//删除
policyRouter.get('/policy/del', (req, res) => {
    let { id } = req.query
    db.query(`DELETE FROM ${table.name} WHERE id = ${id}`, (err, result) => {
        if (err) {
            res.status(500).send(err)
        } else {
            sendResult(res, {
                msg: '删除成功'
            })
        }
    })
})

module.exports = policyRouter